package com.lonty.intelligence.service;

import com.lonty.intelligence.api.Result;
import com.lonty.intelligence.mapper.BuildingMapper;
import com.lonty.intelligence.mapper.HouseholdMapper;
import com.lonty.intelligence.model.Building;
import com.lonty.intelligence.model.Household;
import com.lonty.intelligence.util.UUIDUtil;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.DigestUtils;
import org.springframework.util.StringUtils;
import javax.annotation.Resource;
import java.io.InputStream;

/**
 * @Author 陈璇
 * @Date 2020/5/19 14:09
 * @Version 1.0
 */
@Service
public class ImportDataService {

    @Resource
    private HouseholdMapper householdMapper;

    @Resource
    private BuildingMapper buildingMapper;

    /**
     * 导入数据
     * @param excelFile
     * @param fileName
     * @throws Exception
     */
    @Transactional
    public Result importData(InputStream excelFile, String fileName) throws Exception {
        Workbook workbook = getWorkbook(excelFile, fileName);
        Sheet sheet  = workbook.getSheetAt(0);
        Row titleCell  = sheet.getRow(0);
        for(int i = 1;i < sheet.getLastRowNum();i++){
            Row row = sheet.getRow(i);
            if(row != null && !isAllRowEmpty(row, sheet.getRow(0))){
                Cell name = row.getCell(0);
                Cell idNumber = row.getCell(1);
                Cell gender = row.getCell(2);
                Cell permanentAddress = row.getCell(3);
                Cell residentialAddress = row.getCell(4);
                System.out.println(i);
                System.out.println(isAllRowEmpty(row, sheet.getRow(0)));
                //解析地址
                String permanentAddressStr = permanentAddress.toString();
                //解析道路
                String[] road;
                if(permanentAddressStr.indexOf("弄") != -1){
                    road = permanentAddressStr.split("弄");
                    road[0] = road[0] + "弄";
                }else{
                    road = permanentAddressStr.split("号");
                    road[0] = road[0] + "弄";
                }
                //解析楼栋号
                String[] buildingNumber = new String[2];
                if(road.length > 1){
                    if(road[1] != null){
                        buildingNumber = road[1].split("号");
                        if(buildingNumber.length != 0){
                            buildingNumber[0] = buildingNumber[0] + "号";
                        }
                    }
                }else{
                    buildingNumber[0] = "";
                }
                //解析门牌号
                String[] houseNumber = new String[2];
                if(buildingNumber.length > 1){
                    if(buildingNumber[1] != null){
                        houseNumber = buildingNumber[1].split("室");
                    }
                } else if(road.length == 3){
                    houseNumber = road[2].split("室");
                }else{
                    houseNumber[0] = "";
                }
                //获取小区ID
                String areaId = buildingMapper.getAreaByRole(road[0]);
                //判断楼栋是否存在
                Building building = buildingMapper.getBuilding(road[0], buildingNumber[0], houseNumber[0]);
                //如果楼栋为空则新建
                if(building == null){
                    building = new Building();
                    building.setId(UUIDUtil.randomID());
                    building.setAreaId(areaId);
                    building.setRoad(road[0]);
                    building.setBuildingNumber(buildingNumber[0]);
                    building.setHouseNumber(houseNumber[0]);
                    building.setType(0);
                    building.setIsDel(0);
                    buildingMapper.saveBuilding(building);
                }
                //新建住户
                Household household = new Household();
                household.setId(UUIDUtil.randomID());
                household.setAreaId(areaId);
                household.setBuildingId(building.getId());
                household.setName(name == null ? "" : name.toString());
                household.setIdNumber(idNumber == null ? "" : DigestUtils.md5DigestAsHex(idNumber.toString().getBytes()));
                if(gender != null && !StringUtils.isEmpty(gender.toString())){
                    household.setGender(gender.toString().substring(0,1));
                }
                household.setPermanentAddress(permanentAddressStr);
                household.setResidentialAddress(residentialAddress == null ? "" : residentialAddress.toString());
                household.setIsDel(0);
                householdMapper.saveHousehold(household);
            }
        }
        return Result.builder().build();
    }

    /**
     * 判断文件格式.xls/.xlsx
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public Workbook getWorkbook(InputStream in, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(".xls".equals(fileType)){
            workbook = WorkbookFactory.create(in);
        }else if(".xlsx".equals(fileType)){
            workbook = WorkbookFactory.create(in);
        }else {
            throw new Exception("请上传.xls/.xlsx格式文件！");
        }
        return workbook;
    }

    /**
     * 验证excel是否全部为空
     * @param row 当前行
     * @param firstRow 第一行标题行
     * @return
     */
    public static boolean isAllRowEmpty(Row row,Row firstRow) {
        int count = 0;
        //单元格数量
        int rowCount = 4;
        //判断多少个单元格为空
        for (int c = 0; c < rowCount; c++) {
            Cell cell = row.getCell(c);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || StringUtils.isEmpty((cell+"").trim())){
                count += 1;
            }
        }

        if (count == rowCount) {
            return true;
        }
        return  false;
    }

}
